using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;

using EntLibEC.Cmn;
using EntLibEC.Info.Purchase;

namespace EntLibEC.Dac.Purchase
{
	/// <summary>
	/// Summary description for PODac.
	/// </summary>
	public class PODac
	{
		
		public PODac()
		{
		}

		public int InsertMaster(POInfo oParam)
		{
			string sql = @"INSERT INTO Ent_PO_Master
                            (
                            SysNo, POID, VendorSysNo, StockSysNo, 
                            ShipTypeSysNo, PayTypeSysNo, CurrencySysNo, ExchangeRate, 
                            TotalAmt, CreateTime, CreateUserSysNo, AuditTime, 
                            AuditUserSysNo, InTime, InUserSysNo, IsApportion, 
                            ApportionTime, ApportionUserSysNo, Memo, Note, 
                            Status, ETP, IsConsign
                            )
                            VALUES (
                            @SysNo, @POID, @VendorSysNo, @StockSysNo, 
                            @ShipTypeSysNo, @PayTypeSysNo, @CurrencySysNo, @ExchangeRate, 
                            @TotalAmt, @CreateTime, @CreateUserSysNo, @AuditTime, 
                            @AuditUserSysNo, @InTime, @InUserSysNo, @IsApportion, 
                            @ApportionTime, @ApportionUserSysNo, @Memo, @Note, 
                            @Status, @ETP, @IsConsign
                            )";

			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramPOID = new SqlParameter("@POID", SqlDbType.NVarChar,20);
			SqlParameter paramVendorSysNo = new SqlParameter("@VendorSysNo", SqlDbType.Int,4);
			SqlParameter paramStockSysNo = new SqlParameter("@StockSysNo", SqlDbType.Int,4);
			SqlParameter paramShipTypeSysNo = new SqlParameter("@ShipTypeSysNo", SqlDbType.Int,4);
			SqlParameter paramPayTypeSysNo = new SqlParameter("@PayTypeSysNo", SqlDbType.Int,4);
			SqlParameter paramCurrencySysNo = new SqlParameter("@CurrencySysNo", SqlDbType.Int,4);
			SqlParameter paramExchangeRate = new SqlParameter("@ExchangeRate", SqlDbType.Decimal,9);
			SqlParameter paramTotalAmt = new SqlParameter("@TotalAmt", SqlDbType.Decimal,9);
			SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
			SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int,4);
			SqlParameter paramAuditTime = new SqlParameter("@AuditTime", SqlDbType.DateTime);
			SqlParameter paramAuditUserSysNo = new SqlParameter("@AuditUserSysNo", SqlDbType.Int,4);
			SqlParameter paramInTime = new SqlParameter("@InTime", SqlDbType.DateTime);
			SqlParameter paramInUserSysNo = new SqlParameter("@InUserSysNo", SqlDbType.Int,4);
			SqlParameter paramIsApportion = new SqlParameter("@IsApportion", SqlDbType.Int,4);
			SqlParameter paramApportionTime = new SqlParameter("@ApportionTime", SqlDbType.DateTime);
			SqlParameter paramApportionUserSysNo = new SqlParameter("@ApportionUserSysNo", SqlDbType.Int,4);
			SqlParameter paramMemo = new SqlParameter("@Memo", SqlDbType.NVarChar,500);
			SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,500);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
			SqlParameter paramETP = new SqlParameter("@ETP", SqlDbType.DateTime);
			SqlParameter paramIsConsign = new SqlParameter("@IsConsign", SqlDbType.Int,4);

			if ( oParam.SysNo != AppConst.IntNull)
				paramSysNo.Value = oParam.SysNo;
			else
				paramSysNo.Value = System.DBNull.Value;
			if ( oParam.POID != AppConst.StringNull)
				paramPOID.Value = oParam.POID;
			else
				paramPOID.Value = System.DBNull.Value;
			if ( oParam.VendorSysNo != AppConst.IntNull)
				paramVendorSysNo.Value = oParam.VendorSysNo;
			else
				paramVendorSysNo.Value = System.DBNull.Value;
			if ( oParam.StockSysNo != AppConst.IntNull)
				paramStockSysNo.Value = oParam.StockSysNo;
			else
				paramStockSysNo.Value = System.DBNull.Value;
			if ( oParam.ShipTypeSysNo != AppConst.IntNull)
				paramShipTypeSysNo.Value = oParam.ShipTypeSysNo;
			else
				paramShipTypeSysNo.Value = System.DBNull.Value;
			if ( oParam.PayTypeSysNo != AppConst.IntNull)
				paramPayTypeSysNo.Value = oParam.PayTypeSysNo;
			else
				paramPayTypeSysNo.Value = System.DBNull.Value;
			if ( oParam.CurrencySysNo != AppConst.IntNull)
				paramCurrencySysNo.Value = oParam.CurrencySysNo;
			else
				paramCurrencySysNo.Value = System.DBNull.Value;
			if ( oParam.ExchangeRate != AppConst.DecimalNull)
				paramExchangeRate.Value = oParam.ExchangeRate;
			else
				paramExchangeRate.Value = System.DBNull.Value;
			if ( oParam.TotalAmt != AppConst.DecimalNull)
				paramTotalAmt.Value = oParam.TotalAmt;
			else
				paramTotalAmt.Value = System.DBNull.Value;
			if ( oParam.CreateTime != AppConst.DateTimeNull)
				paramCreateTime.Value = oParam.CreateTime;
			else
				paramCreateTime.Value = System.DBNull.Value;
			if ( oParam.CreateUserSysNo != AppConst.IntNull)
				paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
			else
				paramCreateUserSysNo.Value = System.DBNull.Value;
			if ( oParam.AuditTime != AppConst.DateTimeNull)
				paramAuditTime.Value = oParam.AuditTime;
			else
				paramAuditTime.Value = System.DBNull.Value;
			if ( oParam.AuditUserSysNo != AppConst.IntNull)
				paramAuditUserSysNo.Value = oParam.AuditUserSysNo;
			else
				paramAuditUserSysNo.Value = System.DBNull.Value;
			if ( oParam.InTime != AppConst.DateTimeNull)
				paramInTime.Value = oParam.InTime;
			else
				paramInTime.Value = System.DBNull.Value;
			if ( oParam.InUserSysNo != AppConst.IntNull)
				paramInUserSysNo.Value = oParam.InUserSysNo;
			else
				paramInUserSysNo.Value = System.DBNull.Value;
			if ( oParam.IsApportion != AppConst.IntNull)
				paramIsApportion.Value = oParam.IsApportion;
			else
				paramIsApportion.Value = System.DBNull.Value;
			if ( oParam.ApportionTime != AppConst.DateTimeNull)
				paramApportionTime.Value = oParam.ApportionTime;
			else
				paramApportionTime.Value = System.DBNull.Value;
			if ( oParam.ApportionUserSysNo != AppConst.IntNull)
				paramApportionUserSysNo.Value = oParam.ApportionUserSysNo;
			else
				paramApportionUserSysNo.Value = System.DBNull.Value;
			if ( oParam.Memo != AppConst.StringNull)
				paramMemo.Value = oParam.Memo;
			else
				paramMemo.Value = System.DBNull.Value;
			if ( oParam.Note != AppConst.StringNull)
				paramNote.Value = oParam.Note;
			else
				paramNote.Value = System.DBNull.Value;
			if ( oParam.Status != AppConst.IntNull)
				paramStatus.Value = oParam.Status;
			else
				paramStatus.Value = System.DBNull.Value;
			if ( oParam.ETP != AppConst.DateTimeNull)
				paramETP.Value = oParam.ETP;
			else
				paramETP.Value = System.DBNull.Value;
			//added by entlib 2006/05/23
			if ( oParam.IsConsign != AppConst.IntNull)
				paramIsConsign.Value = oParam.IsConsign;
			else
				paramIsConsign.Value = System.DBNull.Value;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramPOID);
			cmd.Parameters.Add(paramVendorSysNo);
			cmd.Parameters.Add(paramStockSysNo);
			cmd.Parameters.Add(paramShipTypeSysNo);
			cmd.Parameters.Add(paramPayTypeSysNo);
			cmd.Parameters.Add(paramCurrencySysNo);
			cmd.Parameters.Add(paramExchangeRate);
			cmd.Parameters.Add(paramTotalAmt);
			cmd.Parameters.Add(paramCreateTime);
			cmd.Parameters.Add(paramCreateUserSysNo);
			cmd.Parameters.Add(paramAuditTime);
			cmd.Parameters.Add(paramAuditUserSysNo);
			cmd.Parameters.Add(paramInTime);
			cmd.Parameters.Add(paramInUserSysNo);
			cmd.Parameters.Add(paramIsApportion);
			cmd.Parameters.Add(paramApportionTime);
			cmd.Parameters.Add(paramApportionUserSysNo);
			cmd.Parameters.Add(paramMemo);
			cmd.Parameters.Add(paramNote);
			cmd.Parameters.Add(paramStatus);
			cmd.Parameters.Add(paramETP);
			//added by entlib 2006/05/23
			cmd.Parameters.Add(paramIsConsign);

			return SqlHelper.ExecuteNonQuery(cmd);
		}


		public int UpdateMaster(Hashtable paramHash)
		{
			StringBuilder sb = new StringBuilder(200);
			sb.Append("UPDATE Ent_PO_Master SET ");

			if ( paramHash != null && paramHash.Count != 0 )
			{
				int index = 0;
				foreach(string key in paramHash.Keys)
				{
					object item = paramHash[key];
					if ( key.ToLower()=="sysno" )
						continue;

					if ( index != 0 )
						sb.Append(",");
					index++;

					
					if (item is int || item is decimal)
					{
						sb.Append(key).Append("=").Append(item.ToString());
					}
					else if (item is string)
					{
						sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
					}
					else if (item is DateTime)
					{
						sb.Append(key).Append(" = cast(").Append( Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
					}
				}
			}

			sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

			return SqlHelper.ExecuteNonQuery(sb.ToString());
		}

		public int InsertItem(POItemInfo oParam)
		{
			string sql = @"INSERT INTO Ent_PO_Item
                            (
                            POSysNo, ProductSysNo, Quantity, 
                            Weight, OrderPrice, ApportionAddOn, UnitCost
                            )
                            VALUES (
                            @POSysNo, @ProductSysNo, @Quantity, 
                            @Weight, @OrderPrice, @ApportionAddOn, @UnitCost
                            );set @SysNo = SCOPE_IDENTITY();";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramPOSysNo = new SqlParameter("@POSysNo", SqlDbType.Int,4);
			SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int,4);
			SqlParameter paramQuantity = new SqlParameter("@Quantity", SqlDbType.Int,4);
			SqlParameter paramWeight = new SqlParameter("@Weight", SqlDbType.Int,4);
			SqlParameter paramOrderPrice = new SqlParameter("@OrderPrice", SqlDbType.Decimal,9);
			SqlParameter paramApportionAddOn = new SqlParameter("@ApportionAddOn", SqlDbType.Decimal,9);
			SqlParameter paramUnitCost = new SqlParameter("@UnitCost", SqlDbType.Decimal,9);

			paramSysNo.Direction = ParameterDirection.Output;

			if ( oParam.POSysNo != AppConst.IntNull)
				paramPOSysNo.Value = oParam.POSysNo;
			else
				paramPOSysNo.Value = System.DBNull.Value;
			if ( oParam.ProductSysNo != AppConst.IntNull)
				paramProductSysNo.Value = oParam.ProductSysNo;
			else
				paramProductSysNo.Value = System.DBNull.Value;
			if ( oParam.Quantity != AppConst.IntNull)
				paramQuantity.Value = oParam.Quantity;
			else
				paramQuantity.Value = System.DBNull.Value;
			if ( oParam.Weight != AppConst.IntNull)
				paramWeight.Value = oParam.Weight;
			else
				paramWeight.Value = System.DBNull.Value;
			if ( oParam.OrderPrice != AppConst.DecimalNull)
				paramOrderPrice.Value = oParam.OrderPrice;
			else
				paramOrderPrice.Value = System.DBNull.Value;
			if ( oParam.ApportionAddOn != AppConst.DecimalNull)
				paramApportionAddOn.Value = oParam.ApportionAddOn;
			else
				paramApportionAddOn.Value = System.DBNull.Value;
			if ( oParam.UnitCost != AppConst.DecimalNull)
				paramUnitCost.Value = oParam.UnitCost;
			else
				paramUnitCost.Value = System.DBNull.Value;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramPOSysNo);
			cmd.Parameters.Add(paramProductSysNo);
			cmd.Parameters.Add(paramQuantity);
			cmd.Parameters.Add(paramWeight);
			cmd.Parameters.Add(paramOrderPrice);
			cmd.Parameters.Add(paramApportionAddOn);
			cmd.Parameters.Add(paramUnitCost);

			return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
		}
		public int UpdateItem(POItemInfo oParam)
		{
			string sql = @"UPDATE Ent_PO_Item SET 
                            POSysNo=@POSysNo, 
                            ProductSysNo=@ProductSysNo, Quantity=@Quantity, 
                            Weight=@Weight, OrderPrice=@OrderPrice, 
                            ApportionAddOn=@ApportionAddOn, UnitCost=@UnitCost
                            WHERE SysNo=@SysNo";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramPOSysNo = new SqlParameter("@POSysNo", SqlDbType.Int,4);
			SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int,4);
			SqlParameter paramQuantity = new SqlParameter("@Quantity", SqlDbType.Int,4);
			SqlParameter paramWeight = new SqlParameter("@Weight", SqlDbType.Int,4);
			SqlParameter paramOrderPrice = new SqlParameter("@OrderPrice", SqlDbType.Decimal,9);
			SqlParameter paramApportionAddOn = new SqlParameter("@ApportionAddOn", SqlDbType.Decimal,9);
			SqlParameter paramUnitCost = new SqlParameter("@UnitCost", SqlDbType.Decimal,9);

			if ( oParam.SysNo != AppConst.IntNull)
				paramSysNo.Value = oParam.SysNo;
			else
				paramSysNo.Value = System.DBNull.Value;
			if ( oParam.POSysNo != AppConst.IntNull)
				paramPOSysNo.Value = oParam.POSysNo;
			else
				paramPOSysNo.Value = System.DBNull.Value;
			if ( oParam.ProductSysNo != AppConst.IntNull)
				paramProductSysNo.Value = oParam.ProductSysNo;
			else
				paramProductSysNo.Value = System.DBNull.Value;
			if ( oParam.Quantity != AppConst.IntNull)
				paramQuantity.Value = oParam.Quantity;
			else
				paramQuantity.Value = System.DBNull.Value;
			if ( oParam.Weight != AppConst.IntNull)
				paramWeight.Value = oParam.Weight;
			else
				paramWeight.Value = System.DBNull.Value;
			if ( oParam.OrderPrice != AppConst.DecimalNull)
				paramOrderPrice.Value = oParam.OrderPrice;
			else
				paramOrderPrice.Value = System.DBNull.Value;
			if ( oParam.ApportionAddOn != AppConst.DecimalNull)
				paramApportionAddOn.Value = oParam.ApportionAddOn;
			else
				paramApportionAddOn.Value = System.DBNull.Value;
			if ( oParam.UnitCost != AppConst.DecimalNull)
				paramUnitCost.Value = oParam.UnitCost;
			else
				paramUnitCost.Value = System.DBNull.Value;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramPOSysNo);
			cmd.Parameters.Add(paramProductSysNo);
			cmd.Parameters.Add(paramQuantity);
			cmd.Parameters.Add(paramWeight);
			cmd.Parameters.Add(paramOrderPrice);
			cmd.Parameters.Add(paramApportionAddOn);
			cmd.Parameters.Add(paramUnitCost);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int DeleteItem(int poSysNo, int productSysNo)
		{
			string sql = " delete from Ent_PO_Item where posysno = " + poSysNo + " and productsysno = " + productSysNo;
			SqlCommand cmd = new SqlCommand(sql);
			return SqlHelper.ExecuteNonQuery(cmd);

		}
	}
}
